﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace HPortal.Lib
{
    public class DatabaseHelper
    {
        protected static string connectionString_ = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        protected static string connectionUserString_ = System.Configuration.ConfigurationManager.ConnectionStrings["UserConnectionString"].ConnectionString;

        protected static string ShowDetailError_ = System.Configuration.ConfigurationManager.AppSettings["ShowErrorDetail"].ToString();
        public const string GetDate = "GetDate()";
        # region Escape Code

        public static int IsNull(object obValue, int intDefault)
        {
            return isnull(obValue) ? intDefault : (int)obValue;
        }

        public static string GetIDSet(string IDs)
        {
            if (IDs == null)
                return null;

            return "(" + IDs.Trim(new char[] { ' ', ',' }) + ")";
        }

        public static bool isnull(object obValue)
        {
            return (obValue == null || obValue == System.DBNull.Value);
        }

        public static string EscapeName(string S)
        {
            if (S.IndexOfAny(new char[] { '[', ']', '*', '.', ' ' }) != -1)
                return S;
            else
                return "[" + S + "]";
        }

        public static string EscapeQuote(string S)
        {
            return "'" + S.Trim().Replace("'", "''") + "'";
        }

        public static string EscapeQuoteButNotTrim(string S)
        {
            return "'" + S.Replace("'", "''") + "'";
        }

        public static string EscapeQuote(DateTime D)
        {
            return D.ToString("\\'MM-dd-yyyy HH:mm:ss\\'");
        }

        public static string EscapeSmallDateTime(string strDateTime)
        {
            return DateTime.Parse(strDateTime).ToString("\\'MM-dd-yyyy\\'");
        }

        public static string EscapeQuoteDate(string strDateTime)
        {
            return EscapeQuote(DateTime.Parse(strDateTime));
        }


        public static string ParseInt(string s)
        {
            try
            {
                s = s.Trim();
                if (s == string.Empty)
                    return "NULL";
                else
                    return int.Parse(s).ToString();
            }
            catch (Exception)
            {
                return "NULL";
            }
        }

        public static string ParseDouble(string s)
        {
            try
            {
                s = s.Trim();
                if (s == string.Empty)
                    return "NULL";
                else
                    return double.Parse(s).ToString();
            }
            catch (Exception)
            {
                return "NULL";
            }
        }

        public static string EscapeBoolBit(string Str)
        {
            return "(" + Str + ")";
        }

        public static string EscapeUnicode(string Str)
        {
            return "N" + EscapeQuote(Str);
        }

        public static string EscapeQuoteUnicode(string strUni)
        {
            return EscapeUnicode(strUni);
        }

        # endregion

        # region ConvertString Code


        public static string strTCVN3 = "¸µ¶·¹¨¾»¼½Æ©ÊÇÈÉËÐÌÎÏÑªÕÒÓÔÖÝ×ØÜÞãßáâä«èåæçé¬íêëìîóïñòô­øõö÷ùýúûüþ®¸µ¶·¹¡¾»¼½Æ¢ÊÇÈÉËÐÌÎÏÑ£ÕÒÓÔÖÝ×ØÜÞãßáâä¤èåæçé¥íêëìîóïñòô¦øõö÷ùýúûüþ§";
        public static string strUnicode = "áàảãạăắằẳẵặâấầẩẫậéèẻẽẹêếềểễệíìỉĩịóòỏõọôốồổỗộơớờởỡợúùủũụưứừửữựýỳỷỹỵđÁÀẢÃẠĂẮẰẲẴẶÂẤẦẨẪẬÉÈẺẼẸÊẾỀỂỄỆÍÌỈĨỊÓÒỎÕỌÔỐỒỔỖỘƠỚỜỞỠỢÚÙỦŨỤƯỨỪỬỮỰÝỲỶỸỴĐ";

        # endregion

        #region Convert data

        private static string ConvertString(string _strCon1, string _strCon2, string _ConvertStr)
        {
            string strTemp = string.Empty;
            int intPos;
            for (int i = 0; i < _ConvertStr.Length; i++)
            {
                intPos = _strCon1.IndexOf(_ConvertStr[i]);
                if (intPos >= 0)
                    strTemp += _strCon2[intPos];
                else
                    strTemp += _ConvertStr[i];
            }
            return strTemp;
        }

        //Convert TCVN3 to Unicode
        public static string ConvertTcvn3ToUnicode(string ConvertStr)
        {
            if (ConvertStr.Length == 0)
                return ConvertStr;
            return ConvertString(strTCVN3, strUnicode, ConvertStr);
        }

        //Convert Unicode to TCVN3
        public static string ConvertUnicodeToTcvn3(string ConvertStr)
        {
            if (ConvertStr.Length == 0)
                return ConvertStr;
            return ConvertString(strUnicode, strTCVN3, ConvertStr);
        }
        #endregion

        # region Escape Code

        # endregion

        # region  Value Code
        //protected static string connectionString_ = DataHelper.AppSettingsWebConfig("Main.ConnectionString");
        private static string SQLstring_;
        # endregion

        # region  SetConnectionString Code

        public static string ConnectionString
        {
            get { return connectionString_; }
            set { connectionString_ = value; }
        }

        public static string SqlString
        {
            get { return SQLstring_; }
        }
        # endregion

        # region Execute DatabaseHelper Code
        public static SqlDataReader ExecuteReader(string QueryString)
        {
            SqlConnection connection = new SqlConnection(connectionString_);
            connection.Open();
            try
            {
                SqlCommand command = new SqlCommand(QueryString, connection);
                SQLstring_ = command.CommandText;

                SqlDataReader result = command.ExecuteReader();
                return result;
            }
            catch (Exception ex)
            {
                return (SqlDataReader)ShowError(ex);
            }
        }

        public static SqlDataReader ExecuteReader(string StoredProcedure, string[] Parameters)
        {
            string parameters = (Parameters != null && Parameters.Length != 0 ? " " + string.Join(", ", Parameters) : string.Empty);

            return ExecuteReader("EXEC " + StoredProcedure + parameters);
        }

        public static int Execute(string QueryString)
        {
            SqlConnection connection = new SqlConnection(connectionString_);
            connection.Open();
            int result;
            try
            {
                SqlCommand command = new SqlCommand(QueryString, connection);
                SQLstring_ = command.CommandText;
                result = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return (int)ShowError(ex);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }

        private static int Execute(byte[] ImageContent, string ImageType, string queryString)
        {
            //			string ImageType = flImage.PostedFile.ContentType;
            //			int Length = Convert.ToInt32(flImage.PostedFile.InputStream.Length);
            //			byte[] ImageContent = new byte[Length];
            //			flImage.PostedFile.InputStream.Read(ImageContent, 0, Length);
            //
            //			/*******************************/
            //			string SQLQuerry = "INSERT INTO [Image] ([type],[content]) VALUES('" + ImageType + "',@ImageContent)";
            //			string connectionString_ = System.Configuration.ConfigurationSettings.AppSettings["Main.ConnectionString"];   
            //			
            //			SqlConnection Connection = new SqlConnection(connectionString_);
            //			Connection.Open();
            //			SqlCommand Command = new SqlCommand(SQLQuerry , Connection);
            //
            //			SqlParameter ImageContentPara = new SqlParameter("@ImageContent", SqlDbType.Image);
            //			ImageContentPara.Value = ImageContent;
            //			Command.Parameters.Add(ImageContentPara);
            //			Command.ExecuteNonQuery();
            //			Connection.Close();
            try
            {
                SqlConnection Connection = new SqlConnection(connectionString_);
                SqlCommand Command = new SqlCommand(queryString, Connection);

                // Content
                SqlParameter ImageContentPara = new SqlParameter("@ImageContent", SqlDbType.Image);
                ImageContentPara.Value = ImageContent;
                Command.Parameters.Add(ImageContentPara);

                // Type
                SqlParameter ImageTypePara = new SqlParameter("@ImageType", SqlDbType.VarChar);
                ImageTypePara.Value = ImageType;
                Command.Parameters.Add(ImageTypePara);

                SQLstring_ = Command.CommandText;
                Connection.Open();
                int result = Command.ExecuteNonQuery();
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                return (int)ShowError(ex);
            }
        }

        public static int Execute(string StoredProcedure, string[] Parameters)
        {
            string parameters = (Parameters != null && Parameters.Length != 0 ? " " + string.Join(", ", Parameters) : string.Empty);

            return Execute("EXEC " + StoredProcedure + parameters);
        }
        # endregion

        # region Update DatabaseHelper Code

        public static int Update(string[] SetValue, string TableName, string Criterias)
        {
            if (SetValue == null || SetValue.Length == 0)
                return (int)ShowError("Invalid Update");

            string setvalues = string.Join(", ", SetValue);
            string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);

            return Execute(string.Format("UPDATE {1} SET {0}{2}", setvalues, EscapeName(TableName), criterias));
        }

        public static int Update(string[] FieldNames, string[] FieldValues, string TableName, string Criterias)
        {

            if (FieldNames == null || FieldNames.Length == 0 || FieldValues == null || FieldValues.Length == 0 || FieldNames.Length != FieldValues.Length)
                return (int)ShowError("Invalid Update! Fields,Values Error!");

            return Execute(FormatSql_UPDATE(FieldNames, FieldValues, TableName, Criterias));
        }

        public static int Update(string FieldImageType, string FieldImageContent, byte[] ImageContent, string ImageType, string[] FieldNames, string[] FieldValues, string TableName, string Criterias)
        {
            string fieldnames = string.Empty;
            string fieldImage = string.Empty;

            if (FieldImageType != null && FieldImageType != string.Empty & FieldImageContent != null && FieldImageContent != string.Empty)
                fieldImage = FieldImageContent + "= @ImageContent, " + FieldImageType + "= @ImageType";

            FormatSQLString(ref FieldNames, ref FieldValues);
            if (FieldNames != null && FieldNames.Length != 0)
                if (FieldValues != null || FieldValues.Length != 0)
                    if (FieldNames.Length == FieldValues.Length)
                    {
                        string[] setstr = new string[FieldNames.Length];
                        for (int i = 0; i < FieldNames.Length; i++)
                            setstr[i] = FieldNames[i] + "=" + FieldValues[i];
                        fieldnames = ", " + string.Join(" ,", setstr);
                    }

            if (ImageContent.Length == 0)
                return Update(FieldNames, FieldValues, TableName, Criterias);

            string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);

            return Execute(ImageContent, ImageType, string.Format("UPDATE {1} SET {3} {0}{2}", fieldnames, EscapeName(TableName), criterias, fieldImage));
        }

        public static int Update(byte[] ImageContent, string ImageType, string[] FieldNames, string[] FieldValues, string TableName, string Criterias)
        {
            return Update("Type", "Content", ImageContent, ImageType, FieldNames, FieldValues, TableName, Criterias);
        }

        public static int Update(string QueryString)
        {
            return Execute(QueryString);
        }

        # endregion

        /*-------------------------*/
        # region InsertIdentity DatabaseHelper Code

        public static int InsertIdentity(string QueryString)
        {
            SqlConnection connection = new SqlConnection(connectionString_);
            connection.Open();
            try
            {
                SqlCommand command = new SqlCommand(QueryString, connection);
                SQLstring_ = command.CommandText;
                command.ExecuteNonQuery();
                command.CommandText = "SELECT @@Identity";
                SQLstring_ = command.CommandText;
                int result = Convert.ToInt32(command.ExecuteScalar());
                return result;
            }
            catch (Exception ex)
            {
                return (int)ShowError(ex);
            }
            finally
            {
                connection.Close();
            }
        }

        private static int InsertIdentity(byte[] ImageContent, string ImageType, string queryString)
        {
            try
            {
                SqlConnection Connection = new SqlConnection(connectionString_);
                SqlCommand Command = new SqlCommand(queryString, Connection);

                // Content
                SqlParameter ImageContentPara = new SqlParameter("@ImageContent", SqlDbType.Image);
                ImageContentPara.Value = ImageContent;
                Command.Parameters.Add(ImageContentPara);

                // Type
                SqlParameter ImageTypePara = new SqlParameter("@ImageType", SqlDbType.VarChar);
                ImageTypePara.Value = ImageType;
                Command.Parameters.Add(ImageTypePara);

                SQLstring_ = Command.CommandText;
                Connection.Open();
                Command.ExecuteNonQuery();
                Command.CommandText = "SELECT @@Identity";
                SQLstring_ = Command.CommandText;
                int result = Convert.ToInt32(Command.ExecuteScalar());
                Connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                return (int)ShowError(ex);
            }
        }

        public static int InsertIdentity(string[] FieldNames, string[] FieldValues, string TableName)
        {
            if (FieldNames == null || FieldNames.Length == 0 || FieldValues == null || FieldValues.Length == 0 || FieldNames.Length != FieldValues.Length)
                return (int)ShowError("Invalid Insert! Fields,Values Error!");
            return InsertIdentity(FormatSql_INSERT(FieldNames, FieldValues, TableName));
        }

        public static int InsertIdentity(string FieldImageType, string FieldImageContent, byte[] ImageContent, string ImageType, string[] FieldNames, string[] FieldValues, string TableName)
        {
            string fieldNames = string.Empty;
            string fieldValues = string.Empty;
            string fieldImage = string.Empty;

            if (FieldImageType != null && FieldImageType != string.Empty & FieldImageContent != null && FieldImageContent != string.Empty)
                fieldImage = FieldImageContent + ", " + FieldImageType;

            FormatSQLString(ref FieldNames, ref FieldValues);
            if (FieldNames != null && FieldNames.Length != 0)
                if (FieldValues != null || FieldValues.Length != 0)
                    if (FieldNames.Length == FieldValues.Length)
                    {
                        fieldNames = ", " + string.Join(", ", FieldNames);
                        fieldValues = ", " + string.Join(", ", FieldValues);
                    }
            if (ImageContent.Length == 0)
                return InsertIdentity(FieldNames, FieldValues, TableName);

            return InsertIdentity(ImageContent, ImageType, string.Format("INSERT INTO {2} ({3} {0}) VALUES (@ImageContent,@ImageType {1})", fieldNames, fieldValues, EscapeName(TableName), fieldImage));
        }

        public static int InsertIdentity(byte[] ImageContent, string ImageType, string[] FieldNames, string[] FieldValues, string TableName)
        {
            return InsertIdentity("Type", "Content", ImageContent, ImageType, FieldNames, FieldValues, TableName);
        }

        # endregion
        /*-------------------------*/
        # region Insert DatabaseHelper Code
        public static int Insert(string[] FieldNames, string[] FieldValues, string TableName)
        {
            if (FieldNames == null || FieldNames.Length == 0 || FieldValues == null || FieldValues.Length == 0 || FieldNames.Length != FieldValues.Length)
                return (int)ShowError("Invalid Insert! Fields,Values Error!");
            return Execute(FormatSql_INSERT(FieldNames, FieldValues, TableName));
        }

        public static int Insert(string FieldImageType, string FieldImageContent, byte[] ImageContent, string ImageType, string[] FieldNames, string[] FieldValues, string TableName)
        {
            string fieldNames = string.Empty;
            string fieldValues = string.Empty;
            string fieldImage = string.Empty;

            if (FieldImageType != null && FieldImageType != string.Empty & FieldImageContent != null && FieldImageContent != string.Empty)
                fieldImage = FieldImageContent + ", " + FieldImageType;

            FormatSQLString(ref FieldNames, ref FieldValues);
            if (FieldNames != null && FieldNames.Length != 0)
                if (FieldValues != null || FieldValues.Length != 0)
                    if (FieldNames.Length == FieldValues.Length)
                    {
                        fieldNames = ", " + string.Join(", ", FieldNames);
                        fieldValues = ", " + string.Join(", ", FieldValues);
                    }
            if (ImageContent.Length == 0)
                return Insert(FieldNames, FieldValues, TableName);

            return Execute(ImageContent, ImageType, string.Format("INSERT INTO {2} ({3} {0}) VALUES (@ImageContent,@ImageType {1})", fieldNames, fieldValues, EscapeName(TableName), fieldImage));
        }

        public static int Insert(byte[] ImageContent, string ImageType, string[] FieldNames, string[] FieldValues, string TableName)
        {
            return Insert("Type", "Content", ImageContent, ImageType, FieldNames, FieldValues, TableName);
        }

        public static int Insert(string QueryString)
        {
            return Execute(QueryString);
        }
        # endregion

        # region Delete DatabaseHelper Code

        public static int Delete(string TableName, string Criterias)
        {
            return Execute(FormatSql_DELETE(TableName, Criterias));
        }

        public static int Delete(string QueryString)
        {
            return Execute(QueryString);
        }

        # endregion

        # region Drop And Create Table DatabaseHelper Code

        public static int DropTable(string TableName)
        {
            return Execute("DROP TABLE " + TableName);
        }

        //		public static int CreateTable(string[] NewField,string[] FieldType,string[])
        //		{
        //			return Execute(""); 
        //			string strExecute = "CREATE TABLE customer";
        //			strExecute +="(";
        //				cust_id      int            IDENTITY  NOT NULL  PRIMARY KEY,
        //				cust_name    varchar(30)    NOT NULL
        //			strExecute +=")";
        //		}
        //		ALTER TABLE table-name
        //							  DROP COLUMN column-name [,next-column-name]...



        # endregion

        # region Select DatabaseHelper Code

        public static DataTable Select(string QueryString)
        {
            SqlConnection connection = new SqlConnection(connectionString_);
            DataTable table;
            try
            {
                SQLstring_ = QueryString;
                SqlDataAdapter adapter = new SqlDataAdapter(SQLstring_, connection);
                table = new DataTable();
                adapter.Fill(table);
            }
            catch (Exception ex)
            {
                return (DataTable)ShowError(ex);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return table;
        }

        public static DataTable Select(string[] Fields, string TableName, string Criterias, string Priorities)
        {
            return Select(FormatSql_SELECT(Fields, TableName, Criterias, Priorities));
        }

        public static DataTable Select(string[] Fields, string TableName, string Criterias)
        {
            return Select(Fields, TableName, Criterias, null);
        }

        public static DataTable Select(string[] Fields, string TableName)
        {
            return Select(Fields, TableName, null, null);
        }

        # endregion

        # region SelectReader DatabaseHelper Code

        public static SqlDataReader SelectReader(string QueryString)
        {
            SqlConnection connection = new SqlConnection(connectionString_);
            connection.Open();
            try
            {
                SQLstring_ = QueryString;
                SqlCommand command = new SqlCommand(SQLstring_, connection);
                command.CommandTimeout = 1000; //Max timeout is about 15mins
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                if (ShowDetailError_ != null && ShowDetailError_ == "1")
                    throw new Exception(SQLstring_ + " - DatabaseHelper Error! - " + ex.Message);
                else
                    throw new Exception("DatabaseHelper Error! - " + ex.Message);
            }
        }

        public static SqlDataReader SelectReader(string[] Fields, string TableName, string Criterias, string Priorities)
        {
            return SelectReader(FormatSql_SELECT(Fields, TableName, Criterias, Priorities));
        }

        public static SqlDataReader SelectReader(string[] Fields, string TableName, string Criterias)
        {
            return SelectReader(Fields, TableName, Criterias, null);
        }

        public static SqlDataReader SelectReader(string[] Fields, string TableName)
        {
            return SelectReader(Fields, TableName, null, null);
        }

        # endregion

        # region SelectScalar DatabaseHelper Code

        public static object SelectScalar(string QueryString)
        {
            SqlConnection connection = new SqlConnection(connectionString_);
            connection.Open();
            object result;
            try
            {
                SQLstring_ = QueryString;
                SqlCommand command = new SqlCommand(SQLstring_, connection);
                command.CommandTimeout = 1000; //Max timeout is about 15mins
                result = command.ExecuteScalar();
                connection.Close();
                connection.Dispose();
            }
            catch (Exception ex)
            {
                if (ShowDetailError_ != null && ShowDetailError_ == "1")
                    throw new Exception(SQLstring_ + " - DatabaseHelper Error! - " + ex.Message);
                else
                    throw new Exception("DatabaseHelper Error! - " + ex.Message);
            }
            finally
            {
                connection.Close();
                connection.Dispose();
            }
            return result;
        }


        public static object SelectScalarObject(string Field, string TableName, string Criterias)
        {
            string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);
            return SelectScalar(string.Format("SELECT {0} FROM [{1}] {2}", Field, TableName, criterias));
        }

        public static string SelectScalarString(string Field, string TableName, string Criterias)
        {
            object ob = SelectScalarObject(Field, TableName, Criterias);
            if (ob == null || ob == DBNull.Value)
                return string.Empty;
            return ob.ToString();
        }

        public static int SelectScalarInt(string Field, string TableName, string Criterias)
        {
            object ob = SelectScalarObject(Field, TableName, Criterias);
            if (ob == null || ob == DBNull.Value)
                return 0;
            return (int)ob;
        }

        public static bool SelectScalarBool(string Field, string TableName, string Criterias)
        {
            object ob = SelectScalarObject(Field, TableName, Criterias);
            if (ob == null || ob == DBNull.Value)
                return false;
            return (bool)ob;
        }

        public static string SelectScalarStringFromID(string Field, string TableName, int IdValue)
        {
            object o = SelectScalarObjectFromID(Field, TableName, IdValue);

            return (o != DBNull.Value ? (string)o : null);
        }

        public static int SelectScalarIntFromID(string Field, string TableName, int IdValue)
        {
            object o = SelectScalarObjectFromID(Field, TableName, IdValue);

            return (o != DBNull.Value && o != null ? (int)o : -1);
        }

        public static bool SelectScalarBoolFromID(string Field, string TableName, int IdValue)
        {
            object o = SelectScalarObjectFromID(Field, TableName, IdValue);

            return (o != DBNull.Value && o != null ? (bool)o : false);
        }

        public static DateTime SelectScalarDateTimeFromID(string Field, string TableName, int IdValue)
        {
            object o = SelectScalarObjectFromID(Field, TableName, IdValue);

            return (o != DBNull.Value && o != null ? (DateTime)o : DateTime.MinValue);
        }

        public static object SelectScalarObjectFromID(string Field, string TableName, int IdValue)
        {
            return SelectScalar(string.Format("SELECT [{0}] FROM [{1}] WHERE [{1}ID]={2}", Field, TableName, IdValue));
        }

        # endregion

        # region FormatSQLString Code

        private static string FormatSql_SELECT(string[] Fields, string TableName, string Criterias, string Priorities)
        {
            string fields = (Fields != null && Fields.Length != 0 ? string.Join(", ", Fields) : "*");
            string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);
            string priorities = (Priorities != null ? " ORDER BY " + Priorities : string.Empty);

            return string.Format("SELECT {0} FROM {1}{2}{3}", fields, EscapeName(TableName), criterias, priorities);
        }

        private static string FormatSql_DELETE(string TableName, string Criterias)
        {
            string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);
            return string.Format("DELETE {0} {1}", EscapeName(TableName), criterias);
        }

        private static string FormatSql_INSERT(string[] FieldNames, string[] FieldValues, string TableName)
        {
            FormatSQLString(ref FieldNames, ref FieldValues);
            string fieldNames = string.Join(", ", FieldNames);
            string fieldValues = string.Join(", ", FieldValues);

            return string.Format("INSERT INTO {2}({0}) VALUES ({1})", fieldNames, fieldValues, EscapeName(TableName));
        }

        public static string FormatSql_UPDATE(string[] FieldNames, string[] FieldValues, string TableName, string Criterias)
        {

            FormatSQLString(ref FieldNames, ref FieldValues);

            string[] setstr = new string[FieldNames.Length];
            for (int i = 0; i < FieldNames.Length; i++)
                setstr[i] = FieldNames[i] + "=" + FieldValues[i];

            string fieldnames = string.Join(" ,", setstr);

            string criterias = (Criterias != null ? " WHERE " + Criterias : string.Empty);

            return string.Format("UPDATE {1} SET {0}{2}", fieldnames, EscapeName(TableName), criterias);
        }

        private static void FormatSQLString(ref string[] Field, ref string[] Value)
        {
            if (Field != null && Value != null)
                for (int i = 0; i < Field.Length; i++)
                {
                    Field[i] = Field[i].ToLower().Trim();
                    if (Field[i][0] == '@')
                    {
                        Field[i] = Field[i].Substring(1);
                        Value[i] = EscapeQuote(Value[i]);
                    }
                    else if (Field[i][1] == '@')
                    {
                        if (Field[i][0] == 'N' || Field[i][0] == 'n')
                        {
                            Field[i] = Field[i].Substring(2);
                            Value[i] = EscapeUnicode(Value[i]);
                        }
                        else
                        {
                            if (Field[i][0] == 'D' || Field[i][0] == 'd')
                            {
                                Field[i] = Field[i].Substring(2);
                                Value[i] = EscapeSmallDateTime(Value[i]);
                            }
                        }
                    }
                    if (Field[i][0] != '[')
                        Field[i] = EscapeName(Field[i]);
                }
        }
        # endregion
        # region ShowError Code
        public static object ShowError(Exception ex)
        {
            if (ShowDetailError_ != null && ShowDetailError_ == "1")
                throw new Exception(SQLstring_ + " - DatabaseHelper Error! - " + ex.Message);
            else
                throw new Exception("DatabaseHelper Error! - " + ex.Message);
        }

        public static object ShowError(string str)
        {
            throw new Exception("DatabaseHelper Error! - " + str);
        }
        #endregion

        internal static int SelectScalarInt(string p)
        {
            throw new NotImplementedException();
        }
    }
}